#!/usr/bin/python
### This programs is for uploading the ring frags into the database, it checks if the ring fragment already exists and then loads new frags (Agata/Chido/Torsten).

## old scripts screwed up with tautomers (RB)
##introduced dictionary for compound upload -> easier to check for tautomers (RB)
## note: if one compound contains two times the same fragment both are currently uploaded (it might be that get_scaffolds does already delete the duplicates but I am not sure) (RB)


from openeye.oechem import *
import chemistry, operator, mysql

import string,os, sys, mysql, mx.DateTime
import MySQLdb

if len(sys.argv) <> 6:
      print 'mysql_load_templates.py  <username> <password> <data file>  <ringDB (table in which rings are stored)> <name of ring_id_field>'
      sys.exit(1)

username 	= sys.argv[1]
password 	= sys.argv[2]
data_file 	= open(sys.argv[3], 'r')	# data file with fragment_smiles '/t' separated ID of compound
ringDB 		= sys.argv[4]			# table in which the rings are stored			(e.g. unique_ring_fragments_corrected)
ringID 		= sys.argv[5]			# column in ringDB for the ring id 			(e.g. ring_id_fg_u)
date = str(mx.DateTime.localtime().date)

conn=mysql.connect2server(password, username,'purchasable')  			  	  
cursor = conn.cursor ()


### read input file (smiles	id) into array
input_file_array = data_file.readlines()
#print len(input_file_array)

#convert input_file_array to dictionary
compound_dict = {} # = {identifier:[[smiles1], [smiles2], [[smiles3_taut1],[smiles3_taut2]],...}
for i in input_file_array:
	tautomer = False
	new_tautomer = False
	########split ith position in array
	print i
  	smiles, identification = string.split(i.strip(),'\t')
	#check if tautomer
	if not identification[0].isdigit():
		identification = identification[1:]
			
	if not compound_dict.has_key(identification):
		compound_dict[identification] = [smiles]
	else:
		if smiles not in compound_dict[identification]:
			compound_dict[identification].append(smiles)
			


for identifier in compound_dict:
	command = 'select ring_id_fg_u, ring_smiles_fg_u_old from unique_ring_fragments_corrected where ring_id_fg_u = ' + identifier
	cursor.execute(command)
	results = cursor.fetchall()
	if len(results) >= len(compound_dict[identifier]):
		for i in range (0,len(compound_dict[identifier])):
			#print compound_dict[identifier][i], results[i][1], results[i][0]
			command = 'update unique_ring_fragments_corrected set ring_smiles_fg_u ="' +  compound_dict[identifier][i] + '" where ring_smiles_fg_u_old = "' + results[i][1] + '" and ring_id_fg_U = ' + str(results[i][0])
			#print command
			cursor.execute(command)
	else: #have to add new line
		for i in range (0,len(results)):
			#print compound_dict[identifier][i], results[i][1], results[i][0]
			command = 'update unique_ring_fragments_corrected set ring_smiles_fg_u ="' +  compound_dict[identifier][i] + '" where ring_smiles_fg_u_old = "' + results[i][1] + '" and ring_id_fg_U = ' + str(results[i][0])
			#print command
			cursor.execute(command)
		#insert
		for i in range(i+1, len(compound_dict[identifier])):
			command = 'insert into unique_ring_fragments_corrected (ring_id_fg_u, ring_smiles_fg_u, Date_of_update, Updated_by) values(' + identifier + ', "' + compound_dict[identifier][i] + '", "' + date + '", "RB")'
			#print command	
			cursor.execute(command)	
	
			


conn.commit()

#unlock tables (RB)
command = "unlock tables"
cursor.execute(command)
cursor.close()

conn.close ()

